﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class web_Controls_ctrl_KIOS_ntv : System.Web.UI.UserControl
{
    Common _common = null;
    public static string sql_where = "";
    public static string _type = "";
    public static string _id = "";
	Common comm = new Common();
    
    protected void Page_Load(object sender, EventArgs e)
    {
        _common = new Common();
        if (!Page.IsPostBack)
        {
			sql_where="";
            if (Request.Params["type"] != null)
            {
				_type = Request.Params["type"].ToString();
				div_list_by_cat.Visible = true;
                if (_type.Trim().Equals("1"))
                {
                    LoadChucdanh();
                    if (Request.Params["id"] != null)
                    {
                        _id = _common.TextFormat(Request.Params["id"].ToString());
                        LoadNguoitimviecByChucdanh(_id);
                    }
                }
                if (_type.Trim().Equals("2"))
                {
                    LoadHocvan();
                    if (Request.Params["id"] != null)
                    {
                        _id = _common.TextFormat(Request.Params["id"].ToString());
                        LoadNguoitimviecByHocvan(_id);
                    }
                }
                if (_type.Trim().Equals("3"))
                {
                    LoadChuyenMon();
                    if (Request.Params["id"] != null)
                    {
                        _id = _common.TextFormat(Request.Params["id"].ToString());
                        LoadNguoitimviecByChuyenMon(_id);
                    }
                }
            }
            else
            {
                _type = "";
                LoadNguoitimviec();
            }
			
        }
    }
	void LoadChuyenMon()
    {
        string _sql_cd = "select ma_chuyen_mon as MA, ten_chuyen_mon as TEN from DanhMuc_chuyen_mon where ma_chuyen_mon IN "+
            "(SELECT A.ma_chuyen_mon FROM dbo.ConNguoi_hoc_van_chuyen_mon AS A INNER JOIN dbo.ConNguoi_nguoi AS B "+
	        " ON A.ma_nguoi = B.ma_nguoi WHERE B.kich_hoat=1)";
        DataSet _ds = _common.ExeceuteDataset(_sql_cd);
        rpt_list_by.DataSource = _ds;
        rpt_list_by.DataBind();
        if (Request.Params["id"] == null)
        {
            if (_ds.Tables[0].Rows.Count > 0)
            {
                _id = _ds.Tables[0].Rows[0][0].ToString();
                LoadNguoitimviecByChuyenMon(_id);
            }
        }
    }
    void LoadNguoitimviecByChuyenMon(string ma_chuyen_mon)
    {
        DataTable totalrow = null;
        if (sql_where.Trim().Equals(""))
        {
            totalrow = _common.getDataTable("select ma_chuyen_mon from ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B" +
            " on A.ma_nguoi = B.ma_nguoi inner join NguoiTimViec_ho_so as C on C.ma_nguoi=B.ma_nguoi where  B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_chuyen_mon = '" + ma_chuyen_mon + "'");
        }
        else
        {
            totalrow = _common.getDataTable("select ma_chuyen_mon from ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi" +
            "  inner join NguoiTimViec_ho_so as C on C.ma_nguoi=B.ma_nguoi where  B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_chuyen_mon = '" + ma_chuyen_mon + "' and " + sql_where);

        }


        pagination1.ItemCount = totalrow.Rows.Count;
        lbl_total.Text = totalrow.Rows.Count.ToString() + " ứng viên phù hợp với chuyên môn: " + comm.execScalar("select ten_chuyen_mon from DanhMuc_chuyen_mon where ma_chuyen_mon='" + ma_chuyen_mon + "'");
        SqlParameter[] pa = new SqlParameter[7];

        pa[0] = new SqlParameter("@Tables", "  ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi inner join NguoiTimVIec_ho_so as C on C.ma_nguoi=B.ma_nguoi");
        pa[1] = new SqlParameter("@PK", "B.ma_nguoi");
        pa[2] = new SqlParameter("@PageNumber", pagination1.CurrentIndex);
        pa[3] = new SqlParameter("@PageSize", pagination1.PageSize);
        if (sql_where.Trim().Equals(""))
        {
            pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  C.kich_hoat=1 and  A.ma_chuyen_mon = '" + ma_chuyen_mon + "'");
        }
        else
        {
            pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_chuyen_mon = '" + ma_chuyen_mon + "' and " + sql_where);
        }

        //Response.Write("Điều kiện: "+sql_where);
        pa[5] = new SqlParameter("@Fields", "*, convert(varchar, B.ngay_sinh, 103) as ngaysinh_vn, convert(varchar, B.ngay_tao, 103) as ngay_tao_vn");
        pa[6] = new SqlParameter("@Sort", "B.ngay_tao desc");
        DataTable dt = _common.getDataTable(CommandType.StoredProcedure, "dbo.Paging_SubQuery", pa);
        rpt_list.DataSource = dt;
        rpt_list.DataBind();
    }
    void LoadNguoitimviec()
    {
        DataTable totalrow = null;
        if (sql_where.Trim().Equals(""))
        {
            totalrow = _common.getDataTable("select B.ma_nguoi from ConNguoi_nguoi as B inner join NguoiTimViec_ho_so as A ON B.ma_nguoi = A.ma_nguoi where A.kich_hoat=1 and B.kich_hoat=1 ");
        }
        else { totalrow = _common.getDataTable("select B.ma_nguoi from ConNguoi_nguoi as B inner join NguoiTimViec_ho_so as A ON B.ma_nguoi = A.ma_nguoi where  A.kich_hoat=1 and B.kich_hoat=1 and " + sql_where); }
        pagination1.ItemCount = totalrow.Rows.Count;
        lbl_total.Text = totalrow.Rows.Count.ToString();
        SqlParameter[] pa = new SqlParameter[7];

        pa[0] = new SqlParameter("@Tables", "ConNguoi_nguoi as B inner join NguoiTimViec_ho_so as A ON B.ma_nguoi = A.ma_nguoi");
        pa[1] = new SqlParameter("@PK", "B.ma_nguoi");
        pa[2] = new SqlParameter("@PageNumber", pagination1.CurrentIndex);
        pa[3] = new SqlParameter("@PageSize", pagination1.PageSize);
		if (sql_where.Trim().Equals(""))
        {
            pa[4] = new SqlParameter("@Filter"," A.kich_hoat=1 and B.kich_hoat=1");
		}
        else
        {
            pa[4] = new SqlParameter("@Filter"," A.kich_hoat=1 and B.kich_hoat=1 and " +sql_where);
			
        }
        
        pa[5] = new SqlParameter("@Fields", "*, convert(varchar, B.ngay_sinh, 103) as ngaysinh_vn, convert(varchar, B.ngay_tao, 103) as ngay_tao_vn");
		pa[6] = new SqlParameter("@Sort", "B.ngay_tao desc");
        DataTable dt = _common.getDataTable(CommandType.StoredProcedure, "dbo.Paging_SubQuery", pa);
        rpt_list.DataSource = dt;
        rpt_list.DataBind();
    }
    protected void pagination1_Command(object sender, CommandEventArgs e)
    {
		int currnetPageIndx = Convert.ToInt32(e.CommandArgument);
        pagination1.CurrentIndex = currnetPageIndx;
        if (_type.Trim().Equals(""))
        {
            LoadNguoitimviec();
        }
        else
        {
            if (_type.Trim().Equals("1"))
            {
                LoadNguoitimviecByChucdanh(_id);
            }
            if (_type.Trim().Equals("2"))
            {
                LoadNguoitimviecByHocvan(_id);
            }
            if (_type.Trim().Equals("3"))
            {
                LoadNguoitimviecByChuyenMon(_id);
            }
        }
    }
    void LoadChucdanh()
    {
        string _sql_cd = "select ma_chuc_danh as Ma, ten_chuc_danh as TEN from DanhMuc_chuc_danh where ma_chuc_danh IN (select chuc_danh_mong_muon from NguoiTimViec_ho_so where kich_hoat = 1)";
        DataSet _ds = _common.ExeceuteDataset(_sql_cd);
        rpt_list_by.DataSource = _ds;
        rpt_list_by.DataBind();
        if (Request.Params["id"] == null)
        {
            if (_ds.Tables[0].Rows.Count > 0)
            {
                _id = _ds.Tables[0].Rows[0][0].ToString();
                LoadNguoitimviecByChucdanh(_id);
            }
        }
    }
    void LoadHocvan()
    {
        string _sql_cd = "select ma_hoc_van as MA, ten_hoc_van as TEN from DanhMuc_hoc_van where ma_hoc_van IN (select ma_hoc_van from ConNguoi_hoc_van_chuyen_mon where kich_hoat = 1)";
        DataSet _ds = _common.ExeceuteDataset(_sql_cd);
        rpt_list_by.DataSource = _ds;
        rpt_list_by.DataBind();
        if (Request.Params["id"] == null)
        {
            if (_ds.Tables[0].Rows.Count > 0)
            {
                _id = _ds.Tables[0].Rows[0][0].ToString();
                LoadNguoitimviecByHocvan(_id);
            }
        }
    }
    void LoadNguoitimviecByChucdanh(string ma_chuc_danh)
    {
        DataTable totalrow = null;
		if (sql_where.Trim().Equals(""))
        {
            totalrow = _common.getDataTable("select A.ma_ho_so from NguoiTimViec_ho_so as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi where B.kich_hoat=1 and A.kich_hoat=1 and A.chuc_danh_mong_muon = '" + ma_chuc_danh + "'");
        }
        else
        {
            totalrow = _common.getDataTable("select A.ma_ho_so from NguoiTimViec_ho_so as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi where B.kich_hoat=1 and A.kich_hoat=1 and A.chuc_danh_mong_muon = '" + ma_chuc_danh + "' and "+sql_where);
			
        }
        

        pagination1.ItemCount = totalrow.Rows.Count;
        lbl_total.Text = totalrow.Rows.Count.ToString()+ " ứng viên phù hợp với chức danh: "+comm.execScalar("select  ten_chuc_danh from DanhMuc_chuc_danh where ma_chuc_danh='"+ma_chuc_danh+"'");
        SqlParameter[] pa = new SqlParameter[7];

        pa[0] = new SqlParameter("@Tables", " NguoiTimViec_ho_so as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi ");
        pa[1] = new SqlParameter("@PK", "B.ma_nguoi");
        pa[2] = new SqlParameter("@PageNumber", pagination1.CurrentIndex);
        pa[3] = new SqlParameter("@PageSize", pagination1.PageSize);
		if (sql_where.Trim().Equals(""))
        {
            pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  A.kich_hoat=1 and A.chuc_danh_mong_muon = '" + ma_chuc_danh + "'");
        }
        else
        {
            pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  A.kich_hoat=1 and A.chuc_danh_mong_muon = '" + ma_chuc_danh + "' and "+sql_where);
			
        }
		
        pa[5] = new SqlParameter("@Fields", "*, convert(varchar, B.ngay_sinh, 103) as ngaysinh_vn, convert(varchar, B.ngay_tao, 103) as ngay_tao_vn");
		pa[6] = new SqlParameter("@Sort", "B.ngay_tao desc");
        DataTable dt = _common.getDataTable(CommandType.StoredProcedure, "dbo.Paging_SubQuery", pa);
        rpt_list.DataSource = dt;
        rpt_list.DataBind();
    }

    void LoadNguoitimviecByHocvan(string ma_hoc_van)
    {
        DataTable totalrow = null;
		if (sql_where.Trim().Equals(""))
        {
            totalrow = _common.getDataTable("select ma_hoc_van from ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B"+
			" on A.ma_nguoi = B.ma_nguoi inner join NguoiTimViec_ho_so as C on C.ma_nguoi=B.ma_nguoi where  B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_hoc_van = '" + ma_hoc_van + "'");
        }
        else
        {
			totalrow = _common.getDataTable("select ma_hoc_van from ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi"+
			"  inner join NguoiTimViec_ho_so as C on C.ma_nguoi=B.ma_nguoi where  B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_hoc_van = '" + ma_hoc_van + "' and "+sql_where);
			
        }
        

        pagination1.ItemCount = totalrow.Rows.Count;
        lbl_total.Text = totalrow.Rows.Count.ToString()+ " ứng viên phù hợp với trình độ: "+comm.execScalar("select ten_hoc_van from DanhMuc_hoc_van where ma_hoc_van='"+ma_hoc_van+"'");
        SqlParameter[] pa = new SqlParameter[7];

        pa[0] = new SqlParameter("@Tables", "  ConNguoi_hoc_van_chuyen_mon as A inner join ConNguoi_nguoi as B on A.ma_nguoi = B.ma_nguoi inner join NguoiTimVIec_ho_so as C on C.ma_nguoi=B.ma_nguoi");
        pa[1] = new SqlParameter("@PK", "B.ma_nguoi");
        pa[2] = new SqlParameter("@PageNumber", pagination1.CurrentIndex);
        pa[3] = new SqlParameter("@PageSize", pagination1.PageSize);
		if (sql_where.Trim().Equals(""))
        {
            pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  C.kich_hoat=1 and  A.ma_hoc_van = '" + ma_hoc_van + "'");
        }
        else
        {
			pa[4] = new SqlParameter("@Filter", " B.kich_hoat=1 and  C.kich_hoat=1 and A.ma_hoc_van = '" + ma_hoc_van + "' and "+sql_where);		
        }
        
		//Response.Write("Điều kiện: "+sql_where);
        pa[5] = new SqlParameter("@Fields", "*, convert(varchar, B.ngay_sinh, 103) as ngaysinh_vn, convert(varchar, B.ngay_tao, 103) as ngay_tao_vn");
		pa[6] = new SqlParameter("@Sort", "B.ngay_tao desc");
        DataTable dt = _common.getDataTable(CommandType.StoredProcedure, "dbo.Paging_SubQuery", pa);
        rpt_list.DataSource = dt;
        rpt_list.DataBind();
    }


    protected void btn_src_Click(object sender, EventArgs e)
    {
        //string _sql_src = "";
		sql_where="";
        if (radio_src_today.Checked)
        {
            sql_where = " convert(varchar, B.ngay_tao, 103) = '" + DateTime.Now.ToString("dd/MM/yyyy") + "' "; 
        }
        if (radio_src_thisweek.Checked)
        {
            string[] _dayinweek = new string[7];
            _dayinweek[0] = DayOfWeek.Sunday.ToString();
            _dayinweek[1] = DayOfWeek.Monday.ToString();
            _dayinweek[2] = DayOfWeek.Tuesday.ToString();
            _dayinweek[3] = DayOfWeek.Wednesday.ToString();
            _dayinweek[4] = DayOfWeek.Thursday.ToString();
            _dayinweek[5] = DayOfWeek.Friday.ToString();
            _dayinweek[6] = DayOfWeek.Saturday.ToString();
            int _vitri_ngay_truoc = 0;
            int _vitri_ngay_sau = 0;
            for (int i = 1; i <= _dayinweek.Length; i++)
            {
                if (_dayinweek[i-1].Equals(DateTime.Now.DayOfWeek.ToString()))
                {
                    _vitri_ngay_truoc = i;
                    _vitri_ngay_sau = 7 - i;
                }
            }
            sql_where = " B.ngay_tao between '" + DateTime.Now.AddDays(- _vitri_ngay_truoc+1) + "' and '" + DateTime.Now.AddDays(_vitri_ngay_sau) + "' ";
        }
        if (radio_src_thismonth.Checked)
        {
            sql_where = " month(B.ngay_tao) = month(getdate()) and year(B.ngay_tao) = year(getdate()) ";
        }
        if (radio_src_range.Checked)
        {
            if (txt_src_to_RadDatePicker.SelectedDate.HasValue && txt_src_from_RadDatePicker.SelectedDate.HasValue)
            {
                sql_where = " B.ngay_tao between '" + txt_src_from_RadDatePicker.SelectedDate.Value + "' and '" + txt_src_to_RadDatePicker.SelectedDate.Value + "' ";
            }
        }
        if (_type.Trim().Equals(""))
        {
            LoadNguoitimviec();
        }
        else
        {
            if (_type.Trim().Equals("1"))
            {
                LoadNguoitimviecByChucdanh(_id);
            }
            if (_type.Trim().Equals("2"))
            {
                LoadNguoitimviecByHocvan(_id);
            }
        }
    }
}
